
package com.scalar.chiptrack.adaptor.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.io.IOException;

import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.ScalarException;

import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.ShippedLot;
import com.scalar.chiptrack.operations.reports.forms.TransitForm;
import com.scalar.chiptrack.operations.reports.DataDumpReportsHelper;

import org.apache.poi.ss.usermodel.Workbook;


public class ShippedLotDAO implements AdaptorConstants {

    public static void saveShippedLot (DBTransaction dbTransaction, Iterator shippedLotIterator) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.saveShippedLot"));

            ShippedLot shippedLot = null;

            log ("Saving Shipping Information... ");
            while (shippedLotIterator.hasNext())
            {
                shippedLot = (ShippedLot) shippedLotIterator.next();

                if (shippedLot == null) continue;
                if ( shippedLot.getLotNumber() == null) continue;

                log ( shippedLot.toString() );

                pstmt.setString (1, shippedLot.getLotNumber());
                pstmt.setString (2, shippedLot.getVendorLot() );
				pstmt.setString (3, shippedLot.getCustomerPO());
                pstmt.setString (4, shippedLot.getCustomerSO());
                pstmt.setString (5, shippedLot.getInvoiceNumber());
		        pstmt.setString (6, shippedLot.getPartNumber());
		        pstmt.setString (7, shippedLot.getLeadCount());
		        pstmt.setString (8, shippedLot.getPackageType());
		        pstmt.setString (9, shippedLot.getCurrentLocation());
                pstmt.setString (10, shippedLot.getNextLocation());
                pstmt.setString (11, shippedLot.getShipDate());
				pstmt.setString (12, shippedLot.getShipQty());
			    pstmt.setString (13, shippedLot.getMawb());
                pstmt.setString (14, shippedLot.getHawb());
                pstmt.setString (15, shippedLot.getFlightNumber());
                pstmt.setString (16, shippedLot.getEtd());
                pstmt.setString (17, shippedLot.getEta());
                pstmt.setString (18, shippedLot.getVendorName());
                pstmt.setString (19, shippedLot.getProcessType());
                pstmt.setString (20, shippedLot.getWaferID());
                pstmt.setString (21, shippedLot.getFabLotNumber() );
                pstmt.setString (22, shippedLot.getShipDieQty());
                pstmt.setString (23, shippedLot.getShippingDoc());
                pstmt.setString (24, shippedLot.getTester());
                pstmt.setString (25, shippedLot.getTestProgram());
                pstmt.setString (26, shippedLot.isDropShipment() ? "TRUE" :"FALSE" );

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save Shipping Lot Information into database", sqle);
        }
        finally {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    public static void main(String[] args) throws Exception
    {
        /*ArrayList delayedLotsList = loadAllShippingDelayedLots();
        Iterator delayedLots = delayedLotsList.iterator();

        while (delayedLots.hasNext()) {
            System.out.println (delayedLots.next());
        }*/
    }

    /**
     *
     * @param psType
     * @param vendorName
     * @return
     * @throws ScalarException
     */
    public static ArrayList<TransitForm> loadAllTransitLots(String psType, String vendorName) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList<TransitForm> delayedLotList = new ArrayList<TransitForm>();
        ResultSet rset = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "report.loadAllTransitLots" ) );
            pstmt.setString( 1, psType);
            pstmt.setString( 2, vendorName);
            pstmt.setString( 3, "Crystal");

            rset = pstmt.executeQuery();
            TransitForm transitForm = null;

            while ( rset.next() )
            {
                transitForm = new TransitForm();
                transitForm.setPartNumber( rset.getString( "Part Number" ) );
                transitForm.setLotNumber( rset.getString( "Lot Number" ) );
                transitForm.setFabLotNo( rset.getString( "Fab Lot Number" ) );
                transitForm.setPoNumber( rset.getString( "PO Number" ) );
                transitForm.setSoNumber( rset.getString( "SO Number" ) );
                transitForm.setInvoice( rset.getString( "Invoice No" ) );
                transitForm.setNextLocation( rset.getString( "Ship To" ) );
                transitForm.setShipDate( rset.getString( "Ship Date" ) );
                transitForm.setShipQty( rset.getString( "Ship Qty" ) );
                transitForm.setPrevoiusLocation( rset.getString( "From Location" ) );
                transitForm.setProcessName( rset.getString( "Process" ) );
                transitForm.setShippedLotID(rset.getInt("ShippedLot_Id"));
                
                delayedLotList.add( transitForm );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all Transit Lots from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return delayedLotList;
    }
    public static ArrayList loadAllTransitLotsList(DBTransaction dbTransaction, Map paramMap) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList ShipmentList   = new ArrayList();

         try
         {
             if ( dbTransaction != null )
             {
                 conn    = dbTransaction.getConnection();
             }
             else
             {
                 conn    = DataSource.getConnection();
             }
             
             pstmt = conn.prepareStatement( SqlQueryMappings.get( "report.loadAllTransitLots" ) );
             pstmt.setString( 1, (String)paramMap.get("psType"));
             pstmt.setString( 2, (String)paramMap.get("vendorName"));
             pstmt.setString( 3, "XLSREPORT");

             rset    = pstmt.executeQuery();

             ShipmentList = ExcelUtils.getAllResultSetRows(rset, true );

             success = true;
         }
         catch ( Exception e )
         {
             e.printStackTrace();
             throw new ScalarException ( "Could not load Shipment records", e );
         }
         finally
         {
             if ( dbTransaction == null )
             {
                 if ( conn != null )
                 {
                     try
                     {
                         if ( success )
                         {
                             conn.commit();
                         }
                         else
                         {
                             conn.rollback();
                         }
                     }catch ( SQLException e ){}
                 }
             }
             if ( rset != null )
             {
                 try { rset.close(); }catch ( SQLException ignore ) {}
             }
             if ( pstmt != null )
             {
                 try { pstmt.close(); } catch ( SQLException ignore ) {}
             }
             if ( conn != null )
             {
                 try { conn.close(); } catch ( SQLException ignore ) {}
             }
         }
         return ShipmentList;
     }
     public static String loadAllTransitLotsString(DBTransaction dbTransaction, Map paramMap) throws ScalarException
     {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        String csvString = null;

         try
         {
             if ( dbTransaction != null )
             {
                 conn    = dbTransaction.getConnection();
             }
             else
             {
                 conn    = DataSource.getConnection();
             }

             pstmt = conn.prepareStatement(" { CALL sp_load_transit_lots_for_ftp ('0',?,?,?,?,?) }" );
             pstmt.setString( 1, (String)paramMap.get("psType"));
             pstmt.setString( 2, (String)paramMap.get("vendorName"));
             pstmt.setString( 3, (String)paramMap.get("nextLocation"));
             pstmt.setString( 4, (String)paramMap.get("reportType"));
             pstmt.setString( 5, (String)paramMap.get("byJitWindow"));

             rset    = pstmt.executeQuery();

             csvString = ExcelUtils.getCSVString( rset, true );

             success = true;
         }
         catch ( Exception e )
         {
             e.printStackTrace();
             throw new ScalarException ( "Could not load Shipment records", e );
         }
         finally
         {
             if ( dbTransaction == null )
             {
                 if ( conn != null )
                 {
                     try
                     {
                         if ( success )
                         {
                             conn.commit();
                         }
                         else
                         {
                             conn.rollback();
                         }
                     }catch ( SQLException e ){}
                 }
             }
             if ( rset != null )
             {
                 try { rset.close(); }catch ( SQLException ignore ) {}
             }
             if ( pstmt != null )
             {
                 try { pstmt.close(); } catch ( SQLException ignore ) {}
             }
             if ( conn != null )
             {
                 try { conn.close(); } catch ( SQLException ignore ) {}
             }
         }
         return csvString;
     }

    /**
     *
     * @param dbTransaction
     * @param shippingLotID
     * @throws ScalarException
     */
    public static void closeTransitLots(DBTransaction dbTransaction, String[] shippingLotID , String userName ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        boolean success = false;

        if (shippingLotID == null) {
            return;
        }

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt   = conn.prepareCall( SqlQueryMappings.get( "report.closeTransitLots" ) );

            for (int i=0; i<shippingLotID.length; i++)
            {
                cstmt.setString(1, userName);
                cstmt.setInt(2, Integer.parseInt(shippingLotID[i]));

                cstmt.addBatch();
            }

            cstmt.executeBatch();

            success = true;
        }
        catch ( Exception sqle )
        {
            System.out.println( "sqle" + sqle );
            sqle.printStackTrace();
            throw new ScalarException( "Could not Close Transit  Lots from the database", sqle );
        }
        finally
        {
            try
            {
                if ( dbTransaction == null )
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                else
                {
                    if ( success == true )
                    {
                        dbTransaction.commit();
                    }
                    else
                    {
                        dbTransaction.rollback();
                    }
                }
            }
            catch ( SQLException ignore )
            {
            }

            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( cstmt != null )
            {
                try
                {
                    cstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    public static Workbook loadAllTransitLots ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException, IOException
    {
        ArrayList transitLotList = loadAllTransitLotsList (dbTransaction, paramMap );
        return DataDumpReportsHelper.getWorkbook( transitLotList , "Delayed Transit Lots", null, null, null );
    }

    public static String loadAllTransitLotsCSV ( DBTransaction dbTransaction, Map paramMap ) throws ScalarException
    {
        return loadAllTransitLotsString (dbTransaction, paramMap );
    }
    private static Logger s_logger = null;

    private static void log (String message) {

        if (message == null) return;

        if (s_logger != null) {
            synchronized (s_logger) {
                // changed by hareesh on 13th January 2004
                //s_logger.debug (message + LINE_SEPARATOR);
                s_logger.logDebug (message + LINE_SEPARATOR);
            }
        }
        else {
            System.out.println (message);
        }
    }

    public static void setLogger (Logger logger) {
        s_logger = logger;
    }
}

